HumanResourceApplication
package jdbc.hrapp.command;
import java.sql.SQLException;
import java.util.Scanner;
public class HumanResourceApplication {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
System.out.println("1-查询部门员工");
System.out.println("2-办理员工入职");
System.out.println("3-更新员工薪资");
System.out.println("4-员工离职手续");
System.out.println("5-分页查询员工数据");
System.out.println("请选择功能");
Scanner in = new Scanner(System.in);
Integer cmd = in.nextInt();
Command command = null;
switch (cmd){
case 1://查询部门员工
command = new PstmtQueryCommand();
command.execute();
break;
case 2://办理员工入职
command = new InsertCommand();
command.execute();
break;
case 3://更新员工薪资
command = new UpdateCommand();
command.execute();
break;
case 4://离职员工
command = new DeleteCommand();
command.execute();
break;
case 5://分页查询员工数据
command = new PaginationCommand();
command.execute();
break;
}
}
}
PstmtQueryCommand(查询部门员工)
package jdbc.hrapp.command;
import java.sql.*;
import java.util.Scanner;
public class PstmtQueryCommand implements Command{
@Override
public void execute() throws ClassNotFoundException, SQLException {
System.out.println("请输入部门名称: ");
Scanner in = new Scanner(System.in);
String pdname = in.next();
Connection conn = null; //放外面可扩大生命周期
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
//1. 加载并注册侧JDBC驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2. 创建数据库连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&&allowPublicKeyRetrieval=true","root","root");
//3. 创建Statement对象
String sql = "select * from employee where dname=? and eno > ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,pdname);
pstmt.setInt(2,3100);
//结果集
rs = pstmt.executeQuery();
//4. 遍历查询结果
//rs.next() 返回布尔值，代表是否存在下一条记录
//如果有，返回true，同时结果集提取下一条记录
//如果没有，返回false，循环就会停止
while(rs.next()){
Integer eno = rs.getInt(1);//JDBC中字段索引从1开始，而不是0
String ename = rs.getString("ename");
Float salary = rs.getFloat("salary");
String dname = rs.getString("dname");
System.out.println(dname + "-" + eno + "-" + ename + "-" + salary);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally{
if(rs != null){
rs.close();
}
if(pstmt != null){
pstmt.close();
}
if(conn != null && !conn.isClosed()){
conn.close();
}
//5. 关闭连接，释放资源
conn.close();
}
}
}
InsertCommand(办理员工入职)
package jdbc.hrapp.command;
import jdbc.common.DbUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Scanner;
/**
* 新增员工数据
*/
public class InsertCommand implements Command {
@Override
public void execute() throws ClassNotFoundException, SQLException {
Scanner in = new Scanner(System.in);
System.out.println("请输入员工编号: ");
int eno = in.nextInt();
System.out.println("请输入员工姓名: ");
String ename = in.next();
System.out.println("请输入员工薪资: ");
Float salary = in.nextFloat();
System.out.println("请输入隶属部门: ");
String dname = in.next();
System.out.println("请输入入职日期: ");
String strHiredate = in.next();
//String到java.sql.Date分为两步：
//1、String转为java.util.Date
java.util.Date udHiredate = null;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
try {
udHiredate = sdf.parse(strHiredate);
} catch (ParseException e) {
e.printStackTrace();
}
//2、java.util.Date转为java.sql.Date
long time = udHiredate.getTime();//获取自1970年到现在的毫秒数
java.sql.Date sdHiredate = new java.sql.Date(time);
Connection conn = null;
PreparedStatement pstmt = null;
try {
//引用DbUtils类的置入方法
conn = DbUtils.getConnection();
//1、参数化的写操作
String sql = "insert into employee(eno,ename,salary,dname,hiredate) values(?,?,?,?,?)";
//2、对这些参数进行赋值
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,eno);
pstmt.setString(2,ename);
pstmt.setFloat(3,salary);
pstmt.setString(4,dname);
pstmt.setDate(5,sdHiredate);//java.sql.Date
//3、对所有写操作进行更新
int cnt = pstmt.executeUpdate();//所有写操作都使用executeUpdate
System.out.println("cnt: " + cnt);
System.out.println(ename + "员工入职手续已办理");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
DbUtils.closeConnection(null, pstmt, conn);
}
}
}
UpdateCommand(更新员工薪资)
package jdbc.hrapp.command;
import jdbc.common.DbUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;
public class UpdateCommand implements Command{
@Override
public void execute() throws ClassNotFoundException, SQLException {
Scanner in = new Scanner(System.in);
System.out.println("请输入员工编号: ");
int eno = in.nextInt();
System.out.println("请输入员工新的薪资");
float salary = in.nextFloat();
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DbUtils.getConnection();
String sql = "update employee set salary =? where eno=?";
pstmt = conn.prepareStatement(sql);
pstmt.setFloat(1,salary);
pstmt.setInt(2,eno);
int cnt = pstmt.executeUpdate();
if(cnt == 1){
System.out.println("员工薪资调整完毕");
}else {
System.out.println("未找到" + eno + "编号员工数据");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DbUtils.closeConnection(null,pstmt,conn);
}
}
}
DeleteCommand(离职员工)
package jdbc.hrapp.command;
import jdbc.common.DbUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;
public class DeleteCommand implements Command{
@Override
public void execute() throws ClassNotFoundException, SQLException {
Scanner in = new Scanner(System.in);
System.out.println("请输入员工编号: ");
int eno = in.nextInt();
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DbUtils.getConnection();
String sql = "delete from employee where eno =?";
pstmt = conn.prepareStatement(sql);
pstmt.setFloat(1,eno);
int cnt = pstmt.executeUpdate();
if(cnt == 1){
System.out.println("员工离职手续已完成");
}else {
System.out.println("未找到" + eno + "编号员工数据");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DbUtils.closeConnection(null,pstmt,conn);
}
}
}
PaginationCommand(分页查询员工数据)
package jdbc.hrapp.command;
import jdbc.common.DbUtils;
import jdbc.hrapp.entily.Employee;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
/**
* 分页查询员工数据
*/
public class PaginationCommand implements Command{
@Override
public void execute() throws ClassNotFoundException, SQLException {
Scanner in = new Scanner(System.in);
System.out.println("请输入页号：");
int page = in.nextInt();
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
//List每条里面都是Employee实体对象
List<Employee> list = new ArrayList();
try {
conn = DbUtils.getConnection();//从可变参数向后取10个参数
String sql = "select * from employee limit ?,10";
pstmt = conn.prepareStatement(sql); //第一页位置从0~9
pstmt.setInt(1,(page - 1) * 10);
rs = pstmt.executeQuery();
while(rs.next()){
Integer eno = rs.getInt("eno");
String ename = rs.getString("ename");
Float salary = rs.getFloat("salary");
String danme = rs.getString("dname");
Date hiredate = rs.getDate("hiredate");
//每产生一个就封装为employee对象
Employee emp = new Employee();
emp.setEno(eno);
emp.setEname(ename);
emp.setSalary(salary);
emp.setDname(danme);
emp.setHiredate(hiredate);
list.add(emp);
}
System.out.println(list.size());
} catch (Exception e) {
e.printStackTrace();
} finally {
DbUtils.closeConnection(rs,pstmt,conn);
}
}
}
